ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES — Define Default Access Privileges
Synopsis
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES ROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
ALTER DEFAULT PRIVILEGES
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
Description
ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future (it does not affect privileges assigned to already-existing objects). Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered. Among these, functions for which privileges can be set include aggregate functions and procedure functions. When this command is applied to functions, the words FUNCTIONS and ROUTINES are equivalent. (ROUTINES is recommended as it is the standard term that encompasses both functions and procedures. It is not possible to set default privileges separately for functions or procedures.) You can only change default privileges for objects created by yourself or by roles that you are a member of. These privileges can be set globally (i.e., for all objects created in the current database) or only for objects created in specified schemas.
Default privileges for any object type typically grant all grantable privileges to the object owner, and may also grant some privileges to PUBLIC. However, this behavior can be changed by using ALTER DEFAULT PRIVILEGES to modify the global default privileges.
Per-schema default privileges are added to the global default privileges for the particular object type. This means that you cannot revoke per-schema privileges if they have already been granted globally (by default, or by a previous ALTER DEFAULT PRIVILEGES command that did not specify a schema). Per-schema REVOKE is only useful for reversing the effects of a previous per-schema GRANT.
Parameters
target_role
The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.
schema_name
The name of an existing schema. If specified, default privileges for objects created later in that schema are modified. If IN SCHEMA is omitted, the global default privileges are modified. IN SCHEMA cannot be used when setting privileges on schemas, because schemas cannot be nested.
role_name
The name of an existing role to grant or revoke privileges for.
This parameter and all other parameters in abbreviated_grant_or_revoke function as described in GRANT or REVOKE, except that here privileges are set for an entire class of objects rather than specific named objects.
Notes
Use psql's \ddp command to obtain information about existing assignments of default privileges.
If you wish to drop a role for which default privileges have been modified, it is necessary to revoke the changes to its default privileges or use DROP OWNED BY to remove the default privilege entries for the role.
Examples
# Grant SELECT privilege on all tables (and views) you subsequently create in schema myschema, and also allow role webuser to INSERT data into them:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
# Undo the above, so that subsequently created tables will not have any unusual permissions:
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
# Remove the public EXECUTE privilege that is normally granted on functions, for all functions subsequently created by role admin:
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
# Note however that you cannot accomplish this effect with a schema-restricted command. This command does nothing unless it revokes a matching GRANT:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
# This is because per-schema default privileges can only add privileges to the global setting, not remove privileges it grants.